/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Database;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.PeerEntity;

/**
 *
 * @author bangyo17
 */
public class TrackerDBHandler {

    private Connection connect = null;
    private Statement statement = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;
    public String mysqlpass;
    public int portSize = 0;

    public TrackerDBHandler(String pass){
        this.mysqlpass = pass;
    }

    public void init(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connect = DriverManager.getConnection("jdbc:mysql://localhost/KoTorrent?user=root&password="+this.mysqlpass);
            statement = connect.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(TrackerDBHandler.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(TrackerDBHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ArrayList<PeerEntity> HandleRequest(String request){
        ArrayList<PeerEntity> ret = new ArrayList<PeerEntity>();
        try {

            init();
            resultSet = statement.executeQuery("select * from listpeer WHERE FileName = '" + request + "' ORDER BY complete DESC");
            while (this.resultSet.next()) {
                int id = this.resultSet.getInt("ListID");
                String ip = this.resultSet.getString("IP");
                int port = this.resultSet.getInt("Port");
                int isseeder = this.resultSet.getInt("complete");
                boolean iss = false;
                if (isseeder == 1) {
                    iss = true;
                }
                ret.add(new PeerEntity(ip, port, iss));
            }
            //process

        } catch (SQLException ex) {
            Logger.getLogger(TrackerDBHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ret;
    }

    public void updateFileStats(int port, String filename, long uploaded, long downloaded, long left) throws Exception{
        try {
            //cek portnya & filename apakah ada?
            init();
            this.resultSet = this.statement.executeQuery("SELECT * FROM file_stats WHERE port=" + port + " AND filename='" + filename + "'");
            int count=0;
            while(this.resultSet.next()){
                count++;
            }
            if (count == 0){
                System.out.println("insert");
                this.insertRequest(port, filename, uploaded, downloaded, left);
            }
             else if(count==1){
                 System.out.println("update");
                 System.out.println("UPDATE file_stats SET uploaded="+uploaded +",downloaded="+downloaded+",left_="+left
                                                +" WHERE port="+port+" AND filename='"+filename+"'");
                this.statement.executeUpdate("UPDATE file_stats SET uploaded="+uploaded +",downloaded="+downloaded+",left_="+left
                                                +" WHERE port="+port+" AND filename='"+filename+"'");
                
             }
             else{

             }
        } catch (SQLException ex) {
            Logger.getLogger(TrackerDBHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void insertRequest(int port, String filename, long uploaded, long downloaded, long left) throws Exception{
         try {

            Class.forName("com.mysql.jdbc.Driver");
            connect = DriverManager.getConnection("jdbc:mysql://localhost/KoTorrent?user=root&password="+this.mysqlpass);


            // check database from request
            statement = connect.createStatement();
            String query = "INSERT INTO file_stats (port, filename, uploaded, downloaded, left_) VALUES "
                    + "(" + port + ",'" + filename + "' ," + uploaded + "," + downloaded + "," + left + ")";
            statement.executeUpdate(query);
            // System.out.println(query);
            // send list[i] to client

        } catch (Exception e) {
            throw e;
        } finally {
            close();
        }
    }

    public void connect() throws ClassNotFoundException, SQLException{
        Class.forName("com.mysql.jdbc.Driver");
        connect = DriverManager.getConnection("jdbc:mysql://localhost/KoTorrent?user=root&password="+this.mysqlpass);
        statement = connect.createStatement();
    }

    public ArrayList<String> selectPeer(String filename, int com) throws ClassNotFoundException, SQLException{
        ArrayList<String> out = new ArrayList<String>();

        connect();

        resultSet = statement.executeQuery("select * from listpeer WHERE FileName = '" + filename + "' AND complete="+com+" ORDER BY complete DESC");
        int i=0;
        while(resultSet.next()){
            out.add(resultSet.getString("Port"));
            i++;
        }

         return out;
    }

    public void deleteOnePeer(int port) throws ClassNotFoundException, SQLException{
        connect();
        String query = "DELETE FROM listpeer WHERE Port="+port;
        int r = statement.executeUpdate(query);
    }

    public void HandleShareFile(String IP, int Port, String filename, int com) throws Exception {
        try {

            init();
            resultSet = statement.executeQuery("select * from listpeer WHERE IP = '" + IP + "' AND port="+Port);
            int count=0;
            while(resultSet.next()){
                count++;
            }
            if (count == 0){
                System.out.println("inserting...");
                statement.executeUpdate("INSERT INTO listpeer (IP, Port, FileName, complete) "
                        + "VALUES ('"+IP+"','"+Port+"','"+filename+"','"+com+"')");
            }
            //UPDATE jika sudah ada recordnya
             else{
                System.out.println("updating...");
                statement.executeUpdate("UPDATE listpeer SET complete="+ com +
                        " WHERE IP='"+IP+"' AND port="+Port+" AND FileName='"+filename+"'");
             }
        } catch (Exception e) {
            throw e;
        } finally {
            close();
        }
    }

    // You need to close the resultSet
    private void close() {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (statement != null) {
                statement.close();
            }

            if (connect != null) {
                connect.close();
            }
        } catch (Exception e) {
        }
    }

    public static void main(String[] args){
//        try {
//            ArrayList<PeerEntity> ret = new ArrayList<PeerEntity>();
//            TrackerDBHandler tr = new TrackerDBHandler();
//
//            tr.updateFileStats(1234, "laguku.mp3", 12344, 9999, 232);
//        } catch (Exception ex) {
//            Logger.getLogger(TrackerDBHandler.class.getName()).log(Level.SEVERE, null, ex);
//        }
    }
}
